﻿using Sys_PersonnelManagement.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sys_PersonnelManagement.BLL
{
    public static class ActionDB
    {
        private static MSSqlServer db = new MSSqlServer();
        public static bool Login(string name, string pwd) {
            //数据库查询语句的字符串
            string sql = "select * from [dbo].[a_UserInfoTable] where FUserNum='{0}' and FPassword='{1}'";
            //填充数据查询的字符串
            sql = string.Format(sql, name, pwd);
            //接收DAL中的查询方法
            DataTable dt = db.ExecuteSqlDataTable(sql);
            //判断查询出来的dt中是否有数据
            if (dt.Rows.Count > 0)
            {
                //有就是登陆成功
                return true;
            }
            else
            {
                //无就是登陆失败
                return false;
            }
        }
        //根据ID查FUserNum开头
        public static int UserNumSelect(int UserID)
        {
            string sql = "select [FUserNum] from [dbo].[a_UserInfoTable] where UserID=" + UserID;
            string t = db.ExecuteSqlDataTable(sql).Rows[0][0].ToString();
            return int.Parse(t[0].ToString());
        }
        //根据用户名密码查用户ID
        public static int UserIdSelect(string name, string pwd) {
            string sql = " select [UserID] from [dbo].[a_UserInfoTable] where [FUserNum]='{0}' and [FPassword]='{1}'";
            sql = string.Format(sql, name, pwd);
            return (int)db.ExecuteSqlDataTable(sql).Rows[0][0];
        }
        //根据ID查用户名
        public static string UserNameSelect(int UserID)
        {
            string sql = "select [UserName] from [dbo].[a_UserInfoTable] where [UserID]="+UserID;
            return db.ExecuteSqlDataTable(sql).Rows[0][0].ToString();
        }
        //员工管理表查询
        public static DataTable yggl() {
            string sql = "select *,case when UserSex = 1 then '男' else '女' end as sex from a_UserInfoTable";
            return db.ExecuteSqlDataTable(sql);
        }
        //员工管理表添加
        public static bool ygglAdd(int LoginUserID,string UserCode, string UserName, int UserSex, int UserAge, string FUserNum, string FPassword, string Phone, string Email, int UserDeptID) {
            string sql = "insert into a_UserInfoTable values('{0}','{1}',{2},{3},'{4}','{5}','{6}','{7}',{8})";
            sql = string.Format(sql, UserCode, UserName, UserSex, UserAge, FUserNum, FPassword, Phone, Email, UserDeptID);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "添加员工");
            return db.ExecuteEditSql(sql);
        }
        //根据ID查询员工表
        public static DataTable ygglselect(int UserID) {
            string sql = "  select * from [dbo].[a_UserInfoTable] where UserID={0}";
            sql = string.Format(sql, UserID);
            return db.ExecuteSqlDataTable(sql);
        }
        //根据ID，姓名查询员工表
        public static DataTable ygglSearch(string txt_search)
        {
            string sql = "select * from [dbo].[a_UserInfoTable] where UserID={0} or UserName='{0}'";
            try
            {
                int.Parse(txt_search);
            }
            catch (Exception)
            {
                sql = "select * from [dbo].[a_UserInfoTable] where UserID=0 or UserName='{0}'";
            }
            sql = string.Format(sql, txt_search);
            return db.ExecuteSqlDataTable(sql);
        }
        //员工管理表修改
        public static bool ygglUpdate(int LoginUserID, string UserName, int UserSex, int UserAge, string FPassword, string Phone, string Email, int UserDeptID, int UserId) {
            string sql = "update [dbo].[a_UserInfoTable] set UserName='{0}',UserSex={1},UserAge={2},FPassword='{3}',Phone='{4}',Email='{5}',UserDeptID={6} where UserId={7}";
            sql = string.Format(sql, UserName, UserSex, UserAge, FPassword, Phone, Email, UserDeptID, UserId);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "修改员工信息");
            return db.ExecuteEditSql(sql);
        }
        //员工管理表删除
        public static bool ygglDelete(int LoginUserID,int UserID) {
            string sql = "delete from [dbo].[a_UserInfoTable] where UserID={0}";
            sql = string.Format(sql, UserID);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "删除员工信息");
            return db.ExecuteEditSql(sql);
        }

        //部门管理查询
        public static DataTable depart()
        {
            string sql = "select * from [dbo].[a_DeptTable]";
            return db.ExecuteSqlDataTable(sql);
        }
        //添加部门
        public static bool departAdd(int LoginUserID,string DeptName, int ParentID, int DeptHeadID)
        {
            string sql = "insert into a_DeptTable values('{0}',{1},{2})";
            sql = string.Format(sql, DeptName, ParentID, DeptHeadID);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "添加部门");
            return db.ExecuteEditSql(sql);
        }
        //部门管理查询
        public static DataTable departSelect(int DeptID)
        {
            string sql = "select * from [dbo].[a_DeptTable] where DeptID={0}";
            sql = string.Format(sql, DeptID);
            return db.ExecuteSqlDataTable(sql);
        }
        //根据部门主管查询部门表
        public static DataTable departSearch(int DeptHeadID)
        {
            string sql = "  select * from [dbo].[a_DeptTable] where DeptHeadID={0}";
            sql = string.Format(sql, DeptHeadID);
            return db.ExecuteSqlDataTable(sql);
        }
        //部门管理表修改
        public static bool departUpdate(int LoginUserID,int DeptID, string DeptName, int ParentID, int DeptHeadID)
        {
            string sql = "update [dbo].[a_DeptTable] set DeptName='{0}',ParentID={1},DeptHeadID={2} where DeptID={3}";
            sql = string.Format(sql, DeptName, ParentID, DeptHeadID, DeptID);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "修改部门信息");
            return db.ExecuteEditSql(sql);
        }
        //部门管理表删除
        public static bool departDelete(int LoginUserID,int DeptID)
        {
            string sql = "delete from [dbo].[a_DeptTable] where DeptID={0}";
            sql = string.Format(sql, DeptID);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "删除部门信息");
            return db.ExecuteEditSql(sql);
        }
        //休假管理查询
        public static DataTable vacaSelect() {
            string sql = "select T.*,T1.UserName from a_VacationTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        } 
        //添加休假信息
        public static bool vacationAdd(int LoginUserID,int UserID, DateTime StaTime, DateTime EndTime,int DaysNum,int StatesID, string Remark)
        {
            string sql = "insert into a_VacationTable values({0},'{1}','{2}',{3},{4},'{5}')";
            sql = string.Format(sql, UserID, StaTime, EndTime, DaysNum, StatesID, Remark);
            //添加日志
            logAdd(LoginUserID, DateTime.Now, "审批休假信息");
            return db.ExecuteEditSql(sql);
        }
        //事物日志查询
        public static DataTable logSelect(int UserID) {
            string sql = "select T.*,T1.UserName from a_LogTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        }
        //日志添加
        public static bool logAdd(int UserId,DateTime logTime,string logRemark) {
            string sql = "insert into [dbo].[a_LogTable] values({0},'{1}','{2}')";
            sql = string.Format(sql, UserId, logTime, logRemark);
            return db.ExecuteEditSql(sql);
        }

        //考勤查询
        public static DataTable userWork(int UserID) {
            string sql = "select T.*,T1.UserName from a_UserWorkTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        }
        //考勤添加
        public static bool userWorkAdd(int UserID,DateTime sta) {
            string sql = "insert into[dbo].[a_UserWorkTable] values({0},'{1}','{2}')";
            sql = string.Format(sql, UserID, sta, sta);
            logAdd(UserID, DateTime.Now, "成功打卡");
            return db.ExecuteEditSql(sql);
        }
        //考勤下班
        public static bool userWorkUpdate(int userWorkId,int UserID) {
            string sql = "update [dbo].[a_UserWorkTable] set EndDayTime='{0}' where UserWorkID={1}";
            sql = string.Format(sql, DateTime.Now, userWorkId);
            logAdd(UserID, DateTime.Now, "成功下班");
            return db.ExecuteEditSql(sql);
        }
        //加班管理表查询
        public static DataTable OvertimeSelect(int UserID) {
            string sql = "select T.*,T1.UserName from a_OvertimeTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        }
        //加班申请
        public static bool OvertimeAdd(int UserID,DateTime sta,DateTime end,int daysNum,string Remark) {
            string sql = "insert into [dbo].[a_OvertimeTable] values({0},'{1}','{2}',{3},0,'{4}')";
            sql = string.Format(sql, UserID, sta, end, daysNum, Remark);
            logAdd(UserID, DateTime.Now, "申请加班");
            return db.ExecuteEditSql(sql);
        }
        //加班审批
        public static bool OvertimeUpdate(int OvertmeID, int StatesID,int UserID) {
            string sql = "update [dbo].[a_OvertimeTable] set StatesID={0} where OvertimeID={1}";
            sql = string.Format(sql,OvertmeID, StatesID);
            logAdd(UserID, DateTime.Now, "加班审批");
            return db.ExecuteEditSql(sql);
        }
        //工资管理表查询
        public static DataTable wage(int UserID)
        {
            string sql = "select T.*,T1.UserName from a_WageTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        }
        //管理员看到的
        public static DataTable wageAdmin()
        {
            string sql = "select T.*,T1.UserName from a_WageTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        }
        //历史工资发放表查询
        public static DataTable historyWage(int UserID)
        {
            string sql = "select T.*,T1.UserName from a_HistoryWageTable as T left join a_UserInfoTable T1 on T.UserID = T1.UserID";
            return db.ExecuteSqlDataTable(sql);
        }
        //设置薪资如果没有添加
        public static bool WageAdd(int UserID, float money) {
            string sql = "insert into [dbo].[a_WageTable] values({0},'{1}')";
            sql = string.Format(sql, UserID, money);
            logAdd(UserID, DateTime.Now, "设置薪资");
            return db.ExecuteEditSql(sql);
        }
        //已有薪资修改薪资
        public static bool WageUpdate(int UserID, float money) {
            string sql = " update [dbo].[a_WageTable] set [MonthlySalary]={0} where UserID={1}";
            sql = string.Format(sql, money, UserID);
            logAdd(UserID, DateTime.Now, "修改薪资");
            return db.ExecuteEditSql(sql);
        }
        //查询是否已经设置薪资
        public static bool WageSelect(int UserID) {
            string sql = "select * from [dbo].[a_WageTable] where UserID=" + UserID;
            return db.ExecuteSqlDataTable(sql).Rows.Count > 0 ? true : false;
        }
        //历史薪资添加
        public static bool historyWageAdd(int UserID, float money, DateTime date) {
            string sql = "insert into [dbo].[a_HistoryWageTable] values({0},{1},'{2}')";
            sql = string.Format(sql, UserID, money, date);
            logAdd(UserID, DateTime.Now, "发放工资");
            return db.ExecuteEditSql(sql);
        }
    }
}
